package utils;

import Bean.UserInfoBean;
import config.DBConfig;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.sql.*;
public class DBUtils {
    static Connection connection = null;
    static Statement statement = null;
    static PreparedStatement preparedStatement = null;
    static ResultSet resultSet = null;

    private DBUtils(){
        throw new UnsupportedOperationException("no");
    }

    // 查询单个用户信息
    public static UserInfoBean selectUser(String sql) throws SQLException {
        UserInfoBean result = new UserInfoBean();
        System.out.println(sql);
        try {
            connection = DBConfig.getConnection();
            if (connection == null) {
                System.err.println("无法获取数据库连接");
                return result;
            }
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);

            if (!resultSet.next()) {
                System.err.println("SQL语句未返回任何结果: " + sql);
                return null;
            }

            result.setID(resultSet.getInt("ID"));
            result.setName(resultSet.getString("Name"));
            result.setPassword(resultSet.getString("Password"));
            result.setPower(resultSet.getInt("Power"));
            result.setDelete(resultSet.getInt("Del"));
            result.setAsset(resultSet.getBigDecimal("Asset"));
            result.setBankCard(resultSet.getString("BankCard"));
            result.setPwd(resultSet.getString("Pwd"));

        } catch (SQLException e) {
            System.err.println("数据库操作失败: " + e.getMessage());
            resultSet.close();
            statement.close();
            connection.close();
            return null;
        }
        return result;
    }

    // 查询所以为未删除的用户
    public static List<UserInfoBean> selectUsers(){
        List<UserInfoBean> resultList = new ArrayList<>();
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            connection = DBConfig.getConnection();
            statement = connection.createStatement();
            resultSet = statement.executeQuery("select * from userinfo where `Del` = 0");

            while (resultSet.next()) {
                UserInfoBean user = new UserInfoBean();
                user.setID(resultSet.getInt("ID"));
                user.setName(resultSet.getString("Name"));
                user.setPassword(resultSet.getString("Password"));
                user.setPower(resultSet.getInt("Power"));
                user.setDelete(resultSet.getInt("Del"));
                user.setAsset(resultSet.getBigDecimal("Asset"));
                user.setBankCard(resultSet.getString("BankCard"));
                user.setPwd(resultSet.getString("Pwd"));
                resultList.add(user);
            }
        } catch (SQLException e){
            System.out.println("数据库配置错误");
        }

        return resultList;
    }

    // 添加用户信息
    public static boolean insertToUser (UserInfoBean user) {
        try {
            // 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立连接
            Connection connection = DBConfig.getConnection();
            // 创建 SQL 语句
            String sql = "INSERT INTO userinfo (Name,Password,Del,Power,BankCard,Asset , Pwd) VALUES (?,?,?,?,?,?,?)";
            // 创建 PreparedStatement
            preparedStatement = connection.prepareStatement(sql);
            // 设置参数值
            preparedStatement.setString(1, user.getName());
            preparedStatement.setString(2, user.getPassword());
            preparedStatement.setInt(3, user.getDel());
            preparedStatement.setInt(4,user.getPower());
            preparedStatement.setString(5,user.getBankCard());
            preparedStatement.setBigDecimal(6,user.getAsset());
            preparedStatement.setString(7,user.getPwd());
            int rowsAffected = preparedStatement.executeUpdate();
            if (rowsAffected > 0) {
                System.out.println("注册成功！");
            }
            // 关闭 PreparedStatement 和 Connection
            preparedStatement.close();
            connection.close();
            return true;
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    // 更新资产
    public static void updateAsset(BigDecimal asset , int id){
        try {
            connection = DBConfig.getConnection();

            String sql = "UPDATE userinfo SET Asset = ? WHERE ID = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setBigDecimal(1, asset);
            preparedStatement.setInt(2,id);

            int rowsAffected = preparedStatement.executeUpdate();
            if (rowsAffected > 0) {
                System.out.println("资产操作成功");
            }
            preparedStatement.close();
            connection.close();
        } catch (SQLException e) {
            System.err.println("资产操作失败: " + e.getMessage());
        }
    }


    // 更新用户信息
    public static void updateUser(String col , String val , int id){
        try {
            connection = DBConfig.getConnection();

            String sql = "UPDATE userinfo SET "+ col +" = ? WHERE ID = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, val);
            preparedStatement.setInt(2,id);

            int rowsAffected = preparedStatement.executeUpdate();
            if (rowsAffected > 0) {
                System.out.println("用户操作成功");
            }
            preparedStatement.close();
            connection.close();
        } catch (SQLException e) {
            System.err.println("用户操作成功: " + e.getMessage());
        }
    }
}